/**
002
* 基于左右值排序的无限分类算法
003
* 数据库结果为
004
CREATE TABLE om_catagory (
005
CatagoryID int(10) unsigned NOT NULL auto_increment,
006
Name varchar(50) default '',
007
Lft int(10) unsigned NOT NULL default '0',
008
Rgt int(10) unsigned NOT NULL default '0',
009
PRIMARY KEY (id),
010
KEY lft (lft),
011
KEY rgt (rgt)
012
)
013
* 更多的关于左右值排序的例子
014
* http://www.chinaunix.net/jh/27/239532.html(http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)
015
* @author [email]psdshow@yahoo.com.cn[/email]
016
* @version          1.0
017
* @copyright psdshow
018
* 欢迎光临我的个人日志 http://www.dayanmei.com
019
*/
020
class sortclass
021
{
022
 
023
/**
024
* Description
025
* @var
026
* @since          1.0
027
* @access      private
028
*/
029
var $db;
030
 
031
/**
032
* Description
033
* @var
034
* @since          1.0
035
* @access      private
036
*/
037
var $tablefix;
038
 
039
/**
040
* Short description.
041
* 构造函数,引入数据库操作类函数
042
* Detail description
043
* @param          none
044
* @global          none
045
* @since          1.0
046
* @access          private
047
* @return          void
048
* @update          date time
049
*/
050
function sortclass()
051
{
052
global $db;
053
$this->db=$db;
054
$this->tablefix="om_";
055
} // end func
056
 
057
/**
058
* Short description.
059
* 增加新的分类
060
* Detail description
061
* @param          none
062
* @global          none
063
* @since          1.0
064
* @access          private
065
* @return          void
066
* @update          date time
067
*/
068
function addsort($CatagoryID,$SortName)
069
{
070
if($CatagoryID==0){
071
$Lft=0;
072
$Rgt=1;
073
}else{
074
$Result=$this->checkcatagory($CatagoryID);
075
//取得父类的左值,右值
076
$Lft=$Result['Lft'];
077
$Rgt=$Result['Rgt'];
078
$this->db->query("UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`+2 WHERE `Lft`>$Rgt");
079
$this->db->query("UPDATE `".$this->tablefix."catagory` SET `Rgt`=`Rgt`+2 WHERE `Rgt`>=$Rgt");
080
}
081
 
082
//插入
083
if($this->db->query("INSERT INTO `".$this->tablefix."catagory` SET `Lft`='$Rgt',`Rgt`='$Rgt'+1,`Name`='$SortName'")){
084
//$this->referto("成功增加新的类别","JAVASCRIPT:HISTORY.BACK(1)",3);
085
return 1;
086
}else{
087
//$this->referto("增加新的类别失败了","JAVASCRIPT:HISTORY.BACK(1)",3);
088
return -1;
089
}
090
} // end func
091
 
092
/**
093
* Short description.
094
* 删除类别
095
* Detail description
096
* @param          none
097
* @global          none
098
* @since          1.0
099
* @access          private
100
* @return          void
101
* @update          date time
102
*/
103
function deletesort($CatagoryID)
104
{
105
//取得被删除类别的左右值,检测是否有子类,如果有就一起删除
106
$Result=$this->checkcatagory($CatagoryID);
107
$Lft=$Result['Lft'];
108
$Rgt=$Result['Rgt'];
109
//执行删除
110
if($this->db->query("DELETE FROM `".$this->tablefix."catagory` WHERE `Lft`>=$Lft AND `Rgt`<=$Rgt")){
111
$Value=$Rgt-$Lft+1;
112
//更新左右值
113
$this->db->query("UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`-$Value WHERE `Lft`>$Lft");
114
$this->db->query("UPDATE `".$this->tablefix."catagory` SET `Rgt`=`Rgt`-$Value WHERE `Rgt`>$Rgt");
115
//$this->referto("成功删除类别","javascript:history.back(1)",3);
116
return 1;
117
}else{
118
//$this->referto("删除类别失败了","javascript:history.back(1)",3);
119
return -1;
120
}
121
} // end func
122
 
123
/**
124
* Short description.
125
* 1,所有子类,不包含自己;2包含自己的所有子类;3不包含自己所有父类4;包含自己所有父类
126
* Detail description
127
* @param          none
128
* @global          none
129
* @since          1.0
130
* @access          private
131
* @return          void
132
* @update          date time
133
*/
134
function getcatagory($CatagoryID,$type=1)
135
{
136
$Result=$this->checkcatagory($CatagoryID);
137
$Lft=$Result['Lft'];
138
$Rgt=$Result['Rgt'];
139
$SeekSQL="SELECT * FROM `".$this->tablefix."catagory` WHERE ";
140
switch ($type) {
141
case "1":
142
$condition="`Lft`>$Lft AND `Rgt`<$Rgt";
143
break;
144
case "2":
145
$condition="`Lft`>=$Lft AND `Rgt`<=$Rgt";
146
break;
147
case "3":
148
$condition="`Lft`<$Lft AND `Rgt`>$Rgt";
149
break;
150
case "4":
151
$condition="`Lft`<=$Lft AND `Rgt`>=$Rgt";
152
break;
153
default :
154
$condition="`Lft`>$Lft AND `Rgt`<$Rgt";
155
;
156
}
157
$SeekSQL.=$condition." ORDER BY `Lft` ASC";
158
$Sorts=$this->db->getrows($SeekSQL);
159
return $Sorts;
160
} // end func
161
 
162
/**
163
* Short description.
164
* 取得直属父类
165
* Detail description
166
* @param         none
167
* @global         none
168
* @since         1.0
169
* @access         private
170
* @return         void
171
* @update         date time
172
*/
173
function getparent($CatagoryID)
174
{
175
$Parent=$this->getcatagory($CatagoryID,3);
176
return $Parent;
177
} // end func
178
/**
179
* Short description.
180
* 移动类,如果类有子类也一并移动
181
* Detail description
182
* @param         none
183
* @global         none
184
* @since         1.0
185
* @access         private
186
* @return         void
187
* @update         date time
188
*/
189
function movecatagory($SelfCatagoryID,$ParentCatagoryID)
190
{
191
$SelfCatagory=$this->checkcatagory($SelfCatagoryID);
192
$NewCatagory=$this->checkcatagory($ParentCatagoryID);
193
 
194
$SelfLft=$SelfCatagory['Lft'];
195
$SelfRgt=$SelfCatagory['Rgt'];
196
$Value=$SelfRgt-$SelfLft;
197
//取得所有分类的ID方便更新左右值
198
$CatagoryIDS=$this->getcatagory($SelfCatagoryID,2);
199
foreach($CatagoryIDS as $v){
200
$IDS[]=$v['CatagoryID'];
201
}
202
$InIDS=implode(",",$IDS);
203
 
204
$ParentLft=$NewCatagory['Lft'];
205
$ParentRgt=$NewCatagory['Rgt'];
206
//print_r($InIDS);
207
//print_r($NewCatagory);
208
//print_r($SelfCatagory);
209
//exit;
210
if($ParentRgt>$SelfRgt){
211
$UpdateLeftSQL="UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`-$Value-1 WHERE `Lft`>$SelfRgt AND `Rgt`<=$ParentRgt";
212
$UpdateRightSQL="UPDATE `".$this->tablefix."catagory` SET `Rgt`=`Rgt`-$Value-1 WHERE `Rgt`>$SelfRgt AND `Rgt`<$ParentRgt";
213
$TmpValue=$ParentRgt-$SelfRgt-1;
214
$UpdateSelfSQL="UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`+$TmpValue,`Rgt`=`Rgt`+$TmpValue WHERE `CatagoryID` IN($InIDS)";
215
}else{
216
$UpdateLeftSQL="UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`+$Value+1 WHERE `Lft`>$ParentRgt AND `Lft`<$SelfLft";
217
$UpdateRightSQL="UPDATE `".$this->tablefix."catagory` SET `Rgt`=`Rgt`+$Value+1 WHERE `Rgt`>=$ParentRgt AND `Rgt`<$SelfLft";
218
$TmpValue=$SelfLft-$ParentRgt;
219
$UpdateSelfSQL="UPDATE `".$this->tablefix."catagory` SET `Lft`=`Lft`-$TmpValue,`Rgt`=`Rgt`-$TmpValue WHERE `CatagoryID` IN($InIDS)";
220
}
221
$this->db->query($UpdateLeftSQL);
222
$this->db->query($UpdateRightSQL);
223
$this->db->query($UpdateSelfSQL);
224
//$this->referto("成功移动类别","javascript:history.back(1)",3);
225
return 1;
226
} // end func
227
 
228
/**
229
* Short description.
230
*
231
* Detail description
232
* @param         none
233
* @global         none
234
* @since         1.0
235
* @access         private
236
* @return         void
237
* @update         date time
238
*/
239
function checkcatagory($CatagoryID)
240
{
241
//检测父类ID是否存在
242
$SQL="SELECT * FROM `".$this->tablefix."catagory` WHERE `CatagoryID`='$CatagoryID' LIMIT 1";
243
$Result=$this->db->getrow($SQL);
244
if(count($Result)<1){
245
$this->referto("父类ID不存在,请检查","javascript:history.back(1)",3);
246
}
247
return $Result;
248
} // end func
249
 
250
/**
251
* Short description.
252
*
253
* Detail description
254
* @param         none
255
* @global         none
256
* @since         1.0
257
* @access         private
258
* @return         array($Catagoryarray,$Deep)
259
* @update         date time
260
*/
261
function sort2array($CatagoryID=0)
262
{
263
$Output = array();
264
if($CatagoryID==0){
265
$CatagoryID=$this->getrootid();
266
}
267
if(empty($CatagoryID)){
268
return array();
269
exit;
270
}
271
$Result = $this->db->query('SELECT Lft, Rgt FROM `'.$this->tablefix.
272
'catagory` WHERE `CatagoryID`='.$CatagoryID);
273
if($Row = $this->db->fetch_array($Result)) {
274
$Right = array();
275
$Query = 'SELECT * FROM `'.$this->tablefix.
276
'catagory` WHERE Lft BETWEEN '.$Row['Lft'].' AND '.
277
$Row['Rgt'].' ORDER BY Lft ASC';
278
 
279
$Result = $this->db->query($Query);
280
while ($Row = $this->db->fetch_array($Result)) {
281
if (count($Right)>0) {
282
while ($Right[count($Right)-1]<$Row['Rgt']) {
283
array_pop($Right);
284
}
285
}
286
$Output[]=array('Sort'=>$Row,'Deep'=>count($Right));
287
$Right[] = $Row['Rgt'];
288
}
289
}
290
return $Output;
291
} // end func
292
 
293
/**
294
* Short description.
295
*
296
* Detail description
297
* @param         none
298
* @global         none
299
* @since         1.0
300
* @access         private
301
* @return         void
302
* @update         date time
303
*/
304
function getrootid()
305
{
306
$Query="SELECT * FROM`".$this->tablefix."catagory` ORDER BY `Lft` ASC LIMIT 1";
307
$RootID=$this->db->getrow($Query);
308
if(count($RootID)>0){
309
return $RootID['CatagoryID'];
310
}else{
311
return 0;
312
}
313
} // end func
314
 
315
/**
316
* Short description.
317
*
318
* Detail description
319
* @param         none
320
* @global         none
321
* @since         1.0
322
* @access         private
323
* @return         void
324
* @update         date time
325
*/
326
function referto($msg,$url,$sec)
327
{
328
echo "<meta http-equiv="Content-Type" content="text/html; charset=utf-8">";
329
echo "<meta http-equiv=refresh content=$sec;URL=$url>";
330
if(is_array($msg)){
331
foreach($msg as $key=>$value){
332
echo $key."=>".$value."<br>";
333
}
334
}else{
335
echo $msg;
336
}
337
exit;
338
} // end func
339
} // end class
